#region Using
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using CY.GFive.Core.Business;
using CY.Utility.DBUtility;
#endregion Using

namespace CY.GFive.DALProviders.SqlServerProvider
{
	public class TrainProjectProvider : CY.GFive.Core.Providers.DALProvider.ITrainProjectProvider
	{
		#region Sql
		private static readonly string SqlSelectTrainProject = "SELECT [Id], [ProjectCode],[ProjectName],[PjctStartDate],[PjctConsistDate],[PjctEndDate],[Contrator],[Des],[Phone],[Email],[ProjectCate],[Fee],[FeeDes] FROM [TrainProject] WHERE  [Id]=@Id";

		private static readonly string SqlInsertTrainProject = "INSERT INTO [TrainProject]([ProjectCode],[ProjectName],[PjctStartDate],[PjctConsistDate],[PjctEndDate],[Contrator],[Des],[Phone],[Email],[ProjectCate],[Fee],[FeeDes]) values(@ProjectCode,@ProjectName,@PjctStartDate,@PjctConsistDate,@PjctEndDate,@Contrator,@Des,@Phone,@Email,@ProjectCate,@Fee,@FeeDes);SELECT SCOPE_IDENTITY();";

		private static readonly string SqlUpdateTrainProject = "UPDATE [TrainProject] SET [ProjectCode] = @ProjectCode,[ProjectName] = @ProjectName,[PjctStartDate] = @PjctStartDate,[PjctConsistDate] = @PjctConsistDate,[PjctEndDate] = @PjctEndDate,[Contrator] = @Contrator,[Des] = @Des,[Phone] = @Phone,[Email] = @Email,[ProjectCate] = @ProjectCate,[Fee] = @Fee,[FeeDes] = @FeeDes WHERE  [Id]=@Id";

		private static readonly string SqlDeleteTrainProject = "DELETE [TrainProject] WHERE  [Id]=@Id";

		private static readonly string SqlGetAllTrainProject = "SELECT [Id], [ProjectCode],[ProjectName],[PjctStartDate],[PjctConsistDate],[PjctEndDate],[Contrator],[Des],[Phone],[Email],[ProjectCate],[Fee],[FeeDes] FROM [TrainProject]";
		#endregion

		#region ITrainProjectProvider Members
		public Core.Business.TrainProject Select(int id)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@Id", SqlDbType.Int, id);
			SqlDataReader reader = sql.ExecuteSqlReader(SqlSelectTrainProject);

			if (reader != null && !reader.IsClosed && reader.Read())
			{
				Core.Business.TrainProject trainProject = new Core.Business.TrainProject();

				if (!reader.IsDBNull(0)) trainProject.Id = reader.GetInt32(0);
				if (!reader.IsDBNull(1)) trainProject.ProjectCode = reader.GetString(1);
				if (!reader.IsDBNull(2)) trainProject.ProjectName = reader.GetString(2);
				if (!reader.IsDBNull(3)) trainProject.PjctStartDate = reader.GetDateTime(3);
				if (!reader.IsDBNull(4)) trainProject.PjctConsistDate = reader.GetInt32(4);
				if (!reader.IsDBNull(5)) trainProject.PjctEndDate = reader.GetDateTime(5);
				if (!reader.IsDBNull(6)) trainProject.Contrator = reader.GetString(6);
				if (!reader.IsDBNull(7)) trainProject.Des = reader.GetString(7);
				if (!reader.IsDBNull(8)) trainProject.Phone = reader.GetString(8);
				if (!reader.IsDBNull(9)) trainProject.Email = reader.GetString(9);
				if (!reader.IsDBNull(10)) trainProject.ProjectCate = reader.GetString(10);
				if (!reader.IsDBNull(11)) trainProject.Fee = reader.GetDecimal(11);
				if (!reader.IsDBNull(12)) trainProject.FeeDes = reader.GetString(12);

				return trainProject;
			}
			else
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();

				return null;
			}
		}

		public void Insert(Core.Business.TrainProject trainProject)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@ProjectCode", SqlDbType.NVarChar, trainProject.ProjectCode);
			sql.AddParameter("@ProjectName", SqlDbType.NVarChar, trainProject.ProjectName);
			sql.AddParameter("@PjctStartDate", SqlDbType.DateTime, trainProject.PjctStartDate);
			sql.AddParameter("@PjctConsistDate", SqlDbType.Int, trainProject.PjctConsistDate);
			sql.AddParameter("@PjctEndDate", SqlDbType.DateTime, trainProject.PjctEndDate);
			sql.AddParameter("@Contrator", SqlDbType.NVarChar, trainProject.Contrator);
			sql.AddParameter("@Des", SqlDbType.NVarChar, trainProject.Des);
			sql.AddParameter("@Phone", SqlDbType.NVarChar, trainProject.Phone);
			sql.AddParameter("@Email", SqlDbType.NVarChar, trainProject.Email);
			sql.AddParameter("@ProjectCate", SqlDbType.NVarChar, trainProject.ProjectCate);
			sql.AddParameter("@Fee", SqlDbType.Decimal, trainProject.Fee);
			sql.AddParameter("@FeeDes", SqlDbType.NVarChar, trainProject.FeeDes);
			SqlDataReader reader = sql.ExecuteSqlReader(SqlInsertTrainProject);

			if (reader != null && !reader.IsClosed && reader.Read())
			{
				if (!reader.IsDBNull(0)) trainProject.Id = int.Parse(reader.GetDecimal(0).ToString());

				reader.Close();
			}
			else
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void Update(Core.Business.TrainProject trainProject)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@ProjectCode", SqlDbType.NVarChar, trainProject.ProjectCode);
			sql.AddParameter("@ProjectName", SqlDbType.NVarChar, trainProject.ProjectName);
			sql.AddParameter("@PjctStartDate", SqlDbType.DateTime, trainProject.PjctStartDate);
			sql.AddParameter("@PjctConsistDate", SqlDbType.Int, trainProject.PjctConsistDate);
			sql.AddParameter("@PjctEndDate", SqlDbType.DateTime, trainProject.PjctEndDate);
			sql.AddParameter("@Contrator", SqlDbType.NVarChar, trainProject.Contrator);
			sql.AddParameter("@Des", SqlDbType.NVarChar, trainProject.Des);
			sql.AddParameter("@Phone", SqlDbType.NVarChar, trainProject.Phone);
			sql.AddParameter("@Email", SqlDbType.NVarChar, trainProject.Email);
			sql.AddParameter("@ProjectCate", SqlDbType.NVarChar, trainProject.ProjectCate);
			sql.AddParameter("@Fee", SqlDbType.Decimal, trainProject.Fee);
			sql.AddParameter("@FeeDes", SqlDbType.NVarChar, trainProject.FeeDes);
			sql.AddParameter("@Id", SqlDbType.Int, trainProject.Id);
			sql.ExecuteSql(SqlUpdateTrainProject);
		}

		public void Delete(Core.Business.TrainProject trainProject)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@Id", SqlDbType.Int, trainProject.Id);
			sql.ExecuteSql(SqlDeleteTrainProject);
		}

		public IList<Core.Business.TrainProject> GetAllTrainProject()
		{
			IList<Core.Business.TrainProject> trainProjectlist = new List<Core.Business.TrainProject>();
			SqlServerUtility sql = new SqlServerUtility();

			SqlDataReader reader = sql.ExecuteSqlReader(SqlGetAllTrainProject);

			if(reader != null)
			{
				while(reader.Read())
				{
					Core.Business.TrainProject trainProject = new Core.Business.TrainProject();

					if (!reader.IsDBNull(0)) trainProject.Id = reader.GetInt32(0);
					if (!reader.IsDBNull(1)) trainProject.ProjectCode = reader.GetString(1);
					if (!reader.IsDBNull(2)) trainProject.ProjectName = reader.GetString(2);
					if (!reader.IsDBNull(3)) trainProject.PjctStartDate = reader.GetDateTime(3);
					if (!reader.IsDBNull(4)) trainProject.PjctConsistDate = reader.GetInt32(4);
					if (!reader.IsDBNull(5)) trainProject.PjctEndDate = reader.GetDateTime(5);
					if (!reader.IsDBNull(6)) trainProject.Contrator = reader.GetString(6);
					if (!reader.IsDBNull(7)) trainProject.Des = reader.GetString(7);
					if (!reader.IsDBNull(8)) trainProject.Phone = reader.GetString(8);
					if (!reader.IsDBNull(9)) trainProject.Email = reader.GetString(9);
					if (!reader.IsDBNull(10)) trainProject.ProjectCate = reader.GetString(10);
					if (!reader.IsDBNull(11)) trainProject.Fee = reader.GetDecimal(11);
					if (!reader.IsDBNull(12)) trainProject.FeeDes = reader.GetString(12);

					trainProject.MarkOld();
					trainProjectlist.Add(trainProject);
				}
				reader.Close();
			}
			return trainProjectlist;
		}
		#endregion
	}
}
